﻿-- ****************************************************************************************
--                                    FUNCTIONS
--
-- UKLocalTimeFromUtc (function)            -- Get GMT time from UTC
-- fn_GetAutocompleteStatus (function)      -- returns the AutoCompleteStatus 
--
-- ****************************************************************************************

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UKLocalTimeFromUtc]') AND type in (N'FN'))
BEGIN
  DROP function dbo.UKLocalTimeFromUtc
END
GO

CREATE FUNCTION dbo.UKLocalTimeFromUtc 
(
  @Utc DATETIME
)
RETURNS DATETIME
AS
BEGIN
    IF @Utc IS NULL RETURN NULL;

    DECLARE @y INT = DATEPART(YEAR, @Utc);

    -- Last day of March/October
    DECLARE @marchLastDay DATE = EOMONTH(DATEFROMPARTS(@y, 3, 1));
    DECLARE @octLastDay   DATE = EOMONTH(DATEFROMPARTS(@y,10, 1));

    -- Compute last Sunday without relying on DATEFIRST:
    -- DOW with Monday=0..Sunday=6 is (DATEDIFF(day,0,date) % 7)
    -- Days to subtract to get back to Sunday = (dow + 1) % 7
    DECLARE @marchLastSunday DATE = DATEADD(day, -((DATEDIFF(day,0,@marchLastDay)+1) % 7), @marchLastDay);
    DECLARE @octLastSunday   DATE = DATEADD(day, -((DATEDIFF(day,0,@octLastDay)  +1) % 7), @octLastDay);

    -- UK DST windows start/end at 01:00 UTC
    DECLARE @bstStart DATETIME = DATEADD(hour, 1, CAST(@marchLastSunday AS DATETIME)); -- 01:00 UTC
    DECLARE @bstEnd   DATETIME = DATEADD(hour, 1, CAST(@octLastSunday   AS DATETIME)); -- 01:00 UTC

    RETURN CASE
             WHEN @Utc >= @bstStart AND @Utc < @bstEnd
                  THEN DATEADD(hour, 1, @Utc)  -- BST (UTC+1)
                  ELSE @Utc                    -- GMT (UTC+0)
           END;
END;
GO







/****** Object:  UserDefinedFunction [dbo].[fn_GetAutocompleteStatus]    Script Date: 22/09/2025 19:18:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[fn_GetAutocompleteStatus] 
(
	@CourseID INT = 0, 
	@ElementID INT = 0
)
RETURNS VARCHAR(100)
AS
-- ========================================================================================================================
-- Author:		Andrew Breward
-- Create date: 20/09/2025 
-- Descripton:	Scalar function to calculate the AutoCompleteStatus for an Element ID
-- Example:     SELECT ID, dbo.fn_GetAutocompleteStatus(CourseID, ID) As AutoCompleteStatus FROM OGP_Element WHERE CourseID = 29
--
-- ========================================================================================================================
BEGIN
    --these are the possible return values (we Never return '')
    DECLARE @Ret VARCHAR(100)           = 'UNKNOWN';
	DECLARE @IncompleteText VARCHAR(10) = 'INCOMPLETE';
	DECLARE @OkText VARCHAR(2)          = 'OK';
	DECLARE @OffText VARCHAR(3)         = 'OFF';
	DECLARE @CriteriaText VARCHAR(8)    = 'CRITERIA';
	DECLARE @NoCriteriaText VARCHAR(10) = 'NOCRITERIA';

    -- Quick exit if invalid parameters
    IF @CourseID = 0 OR @ElementID = 0 
	BEGIN
		RETURN 'ERROR: Missing Parameter';
	END

	-- Check if Course & Element IDs exist
	IF NOT EXISTS (SELECT 1 FROM OGP_Element WHERE CourseID = @CourseID AND ID = @ElementID)
	BEGIN
		RETURN 'ERROR: Element ID (' + Convert(VARCHAR, @ElementID) + ') Not Found for Course ID (' + Convert(VARCHAR, @CourseID) + ')';
	END

    -- Get AutoCompleteType from course options
    DECLARE @AutoCompleteType VARCHAR(100) = 
    (
        SELECT TOP 1 ISNULL(OD.Description, '')
        FROM OGP_MarkbookCourseOption CO
        INNER JOIN OGP_MarkbookOptionDetail OD ON CO.MarkbookOptionDetailID = OD.ID
        WHERE CO.CourseID = @CourseID AND CO.MarkbookOptionID = 0
    );

    -- Exit if no autocomplete type
    IF IsNull(@AutoCompleteType,'') = '' 
	BEGIN
		RETURN @OffText;
	END
	
	--Check if Criteria
	IF EXISTS (SELECT 1 FROM OGP_Element WHERE CourseID = @CourseID AND OGP_ElementTypeID = 3 AND  ID = @ElementID)
	BEGIN
		RETURN @CriteriaText;
	END

	--Check for no Criteria
	IF NOT EXISTS (SELECT 1 FROM OGP_Element WHERE CourseID = @CourseID AND OGP_ElementTypeID = 3 AND RecordStatus != 'Obsolete' AND (DirectParentID = @ElementID OR TopLevelParentID = @ElementID))
	BEGIN
		RETURN @NoCriteriaText;
	END

    -- Get the element type
    --DECLARE @ElementTypeID INT;
    --SELECT @ElementTypeID = OGP_ElementTypeID
    --FROM OGP_Element
    --WHERE ID = @ElementID;

    -- Only process if it's a valid type (1 or 2)
    --IF @ElementTypeID > 2 RETURN '';

	--Check which AutoComplete Type we have
	IF (@AutoCompleteType = 'PMD')
	BEGIN
		-- Identify all relevant Process IDs (the current element and its children)
		WITH RelevantElements AS (
			SELECT ID
			FROM OGP_Element
			WHERE CourseID = @CourseID
			  AND OGP_ElementTypeID <= 2
			  AND RecordStatus != 'Obsolete'
			  AND (
				  ID = @ElementID 
				  OR DirectParentID = @ElementID 
				  OR TopLevelParentID = @ElementID
			  )
		),
		CriteriaCounts AS (
			SELECT 
				RE.ID AS ProcessID,
				SUM(CASE WHEN E.ElementCode LIKE 'P%' THEN 1 ELSE 0 END) AS HasP,
				SUM(CASE WHEN E.ElementCode LIKE 'M%' THEN 1 ELSE 0 END) AS HasM,
				SUM(CASE WHEN E.ElementCode LIKE 'D%' THEN 1 ELSE 0 END) AS HasD
			FROM RelevantElements RE
			LEFT JOIN OGP_Element E 
				ON (E.DirectParentID = RE.ID OR E.TopLevelParentID = RE.ID)
				AND E.OGP_ElementTypeID = 3
			GROUP BY RE.ID
		)
		SELECT @Ret = 
			CASE 
				WHEN EXISTS (
					SELECT 1  FROM CriteriaCounts  WHERE (HasP = 0)  -- missing P
				) THEN @IncompleteText
				ELSE @OkText
			END;

	END
	ELSE IF (@AutoCompleteType = 'A*-D')
	BEGIN
		-- Identify all relevant Process IDs (the current element and its children)
		WITH RelevantElements AS (
			SELECT ID
			FROM OGP_Element
			WHERE CourseID = @CourseID
			  AND OGP_ElementTypeID <= 2
			  AND (
				  ID = @ElementID 
				  OR DirectParentID = @ElementID 
				  OR TopLevelParentID = @ElementID
			  )
		),
		CriteriaCounts AS (
			SELECT 
				RE.ID AS ProcessID,
				SUM(CASE WHEN E.ElementCode LIKE 'A*%' THEN 1 ELSE 0 END) AS HasAStar,
				SUM(CASE WHEN (E.ElementCode LIKE 'A%' AND E.ElementCode NOT LIKE 'A*%') THEN 1 ELSE 0 END) AS HasA,
				SUM(CASE WHEN E.ElementCode LIKE 'B%' THEN 1 ELSE 0 END) AS HasB,
				SUM(CASE WHEN E.ElementCode LIKE 'C%' THEN 1 ELSE 0 END) AS HasC,
				SUM(CASE WHEN E.ElementCode LIKE 'D%' THEN 1 ELSE 0 END) AS HasD
			FROM RelevantElements RE
			LEFT JOIN OGP_Element E 
				ON (E.DirectParentID = RE.ID OR E.TopLevelParentID = RE.ID)
				AND E.OGP_ElementTypeID = 3
			GROUP BY RE.ID
		)
		SELECT @Ret = 
			CASE 
				WHEN EXISTS (
					SELECT 1  FROM CriteriaCounts  WHERE (HasD = 0)  -- missing D
				) THEN @IncompleteText
				--WHEN EXISTS (
				--	SELECT 1  FROM CriteriaCounts  WHERE (HasD >= 1 AND (HasC = 0 OR HasB = 0 OR HasA = 0) AND HasAStar >= 1)  -- P,D (M)
				--) THEN @IncompleteText
				--WHEN EXISTS (
				--	SELECT 1  FROM CriteriaCounts  WHERE (HasD >= 1 AND HasC >= 1 AND (HasB = 0 OR HasA = 0) AND HasAStar >= 1)  -- P,D (M)
				--) THEN @IncompleteText
				--WHEN EXISTS (
				--	SELECT 1  FROM CriteriaCounts  WHERE (HasD >= 1 AND HasC >= 1 AND HasB >= 1 AND (HasA = 0) AND HasAStar >= 1)  -- P,D (M)
				--) THEN @IncompleteText
				ELSE @OkText
			END;

	END
	ELSE IF (@AutoCompleteType = 'Pass Only')
	BEGIN
		-- Identify all relevant Process IDs (the current element and its children)
		WITH RelevantElements AS (
			SELECT ID
			FROM OGP_Element
			WHERE CourseID = @CourseID
			  AND OGP_ElementTypeID <= 2
			  AND (
				  ID = @ElementID 
				  OR DirectParentID = @ElementID 
				  OR TopLevelParentID = @ElementID
			  )
		),
		CriteriaCounts AS (
			SELECT 
				RE.ID AS ProcessID,
				SUM(CASE WHEN E.ElementCode != '' THEN 1 ELSE 0 END) AS HasAnything
			FROM RelevantElements RE
			LEFT JOIN OGP_Element E 
				ON (E.DirectParentID = RE.ID OR E.TopLevelParentID = RE.ID)
				AND E.OGP_ElementTypeID = 3
			GROUP BY RE.ID
		)
		SELECT @Ret = 
			CASE 
				WHEN EXISTS (
					SELECT 1  FROM CriteriaCounts  WHERE (HasAnything > 0)  
				) THEN @OkText
				ELSE @IncompleteText
			END;

	END


    RETURN @Ret;
END;
GO
